package org.restorator.dao.jdbc;

import org.restorator.dao.base.GenericDao;
import org.restorator.dao.tx.TransactionManager;
import org.restorator.dao.tx.TransactionManagerJdbc;
import org.restorator.exception.DataStoreException;
import org.restorator.utill.GeneralUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.Serializable;
import java.sql.*;
import java.util.Collection;
import java.util.List;

import static com.google.common.base.Preconditions.checkNotNull;

/**
 * Template method for jdbc dao level.
 *
 * @author Snisar Bogdan
 */
public abstract class AbstractJdbsTemplate<E, ID extends Serializable>
        implements GenericDao<E, ID> {

    private final TransactionManager tx = TransactionManagerJdbc.getInstance();
    private static Logger logger = LoggerFactory.getLogger(GeneralUtil.getClassName());


    protected AbstractJdbsTemplate(TransactionManager tx) {
    }

    public AbstractJdbsTemplate() { }

    protected Connection getConnection() throws DataStoreException{
            return tx.getCurrentConnection();
    }

    /**
     *
     *
     * @param sql query
     * @param extractor {@link ResultSetExtractor} with some {@link RowMapper}
     * @param enricher some {@link Enricher} for references
     * @return list entities
     * @throws DataStoreException
     */
    @SuppressWarnings("unchecked")
    protected List<E> list(
            String sql,
            ResultSetExtractor extractor,
            Enricher<E> enricher
            ) throws DataStoreException {

        logger.debug("Execution of list");
        Connection connection;
        Statement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution list() of list with sql {}, extractor {} and enricher {}", sql, extractor, enricher);

            connection = getConnection();
            statement = connection.createStatement();
            logger.debug("Get connection and executing sql {}", sql);
            rs = statement.executeQuery(sql);

            logger.debug("Extract by extractor {} and then enrich by {}", extractor, enricher);
            List<E> list = (List<E>) extractor.extractData(rs); //todo
            for(E e : list) {
                logger.debug("Enrich object {}", e);
                enricher.enrich(e);
            }

            return list;
        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }

    /**
     *  Get list by parametrize sql.
     *
     * @param sql query
     * @param extractor {@link ResultSetExtractor} with some {@link RowMapper}
     * @param enricher some {@link Enricher} for references
     * @param ps Setter for parametrized sql {@link PreparedSetter}
     * @return list entities
     * @throws DataStoreException
     */
    @SuppressWarnings("unchecked")
    protected List<E> executePreparList(
            String sql,
            ResultSetExtractor extractor,
            Enricher<E> enricher,
            PreparedSetter ps) throws DataStoreException {
        checkNotNull(extractor, "Null extractor");
        checkNotNull(enricher, "Null enricher");
        checkNotNull(ps, "Null prepared setter");

        Connection connection;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            logger.debug("Execution executePreparList() of list with sql {}, extractor {} and enricher {}", sql, extractor, enricher);
            ps.setParameters(statement);
            rs = statement.executeQuery();

            List<E> list = (List<E>) extractor.extractData(rs);
            for(E e : list) {
                enricher.enrich(e);
            }

            return list;
        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);//todo не закрывать, атолько в tx
            GeneralUtil.closeInSilence(statement);
        }
    }

    /**
     * Get one object by sql
     *
     * @param sql query
     * @param extractor {@link ResultSetExtractor} with some {@link RowMapper}
     * @param enricher some {@link Enricher} for references
     * @return object E
     * @throws DataStoreException
     */
    @SuppressWarnings("unchecked")
    protected E execute(
            String sql,
            ResultSetExtractor extractor,
            Enricher<E> enricher) throws DataStoreException {
        checkNotNull(extractor, "Null extractor");
        checkNotNull(enricher, "Null enricher");

        Connection connection;
        Statement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution execute() with sql {}, extractor {} and enricher {}", sql, extractor, enricher);
            connection = getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            return (E) extractor.extractFirst(rs);
        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }

    /**
     *
     *
     * @param sql
     * @param extractor
     * @param enricher
     * @param pr
     * @return
     * @throws DataStoreException
     */
    @SuppressWarnings("unchecked")
    protected E executeParameter(
            String sql,
            ResultSetExtractor extractor,
            Enricher<E> enricher,
            PreparedSetter pr) throws DataStoreException {
        checkNotNull(extractor, "Null extractor");
        checkNotNull(enricher, "Null enricher");
//        logger.debug("Execution executeParameter() with sql {}, with {} and enricher {}", sql, extractor, enricher);

        Connection connection;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution executeParameter() with sql {}, extractor {} and enricher {} and PreparedSetter {}", sql, extractor, enricher, pr);
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            pr.setParameters(statement);
            rs = statement.executeQuery();
            logger.trace("Result set: {}", rs);
            E e = (E) extractor.extractFirst(rs);
            enricher.enrich(e);
            return e;
        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }

    /**
     * Number of rows.
     *
     * @param tableName Table name!
     * @return number of rows
     * @throws DataStoreException
     */
    protected long rowCount(String tableName) throws DataStoreException {
        Connection connection;
        Statement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution rowCount() to table {}", tableName);

            connection = getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery("SELECT COUNT() FROM " + tableName);

            if(rs != null && rs.next()) {
                return rs.getLong(1);
            } else {
                return -1;
            }
        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }

    /**
     * Возвращает null???
     *
     * @param sql query
     * @param enricher some {@link Enricher} for references
     * @return {@link ResultSet} of generated keys
     * @throws DataStoreException
     */
//    todo Не доделанно!
    protected ResultSet insertAndGetKeys(String sql, E object, Enricher<E> enricher) throws DataStoreException {
        checkNotNull(enricher, "Null enricher");

        Connection connection;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution insertAndGetKeys() of list with sql {}, and enricher {}", sql, enricher);
            connection = getConnection();
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            rs = statement.executeQuery(sql);
            enricher.insert(object);

        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
        return null;
    }

    /**
     *
     * @param sql
     * @param object
     * @param enricher
     * @param pr
     *
     * @return generated key or 0 as result {key as int}
     * @throws DataStoreException
     */
    protected int executeInsert(String sql, E object, Enricher<E> enricher, PreparedSetter pr) throws DataStoreException  {
        checkNotNull(enricher, "Null enricher");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution executeInsert() of list with sql {}, and enricher {} and PreparedSetter {}", sql, enricher, pr);

            connection = getConnection();
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pr.setParameters(statement);
            statement.execute();
            enricher.insert(object);
            ResultSet keys = statement.getGeneratedKeys();
            if(keys.next()) {
                return keys.getInt(1);
            } else {
                return 0;
            }
        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }

    /**
     *  Execute insert in batch.
     *
     * @param sql query
     * @param object collection of objects
     * @param setter {@link PreparedSetter}
     * @param enricher enricher
     * @throws DataStoreException if some SQLException will happened
     */
    protected void executeInsertBatch(String sql, Collection<E> object, PreparedSetter setter, Enricher<E> enricher) throws DataStoreException  {
        checkNotNull(object, "Null collection");
        checkNotNull(enricher, "Null enricher");
        checkNotNull(setter, "Null prepared setter");


        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution executeInsertBatch() with sql {}, and and PreparedSetter {}", sql, object, setter);

            connection = getConnection();
            if(!connection.getAutoCommit()) {
                connection.setAutoCommit(false);
            }

            for(E el : object)  {
                enricher.insert(el); //todo
                statement = connection.prepareStatement(sql);
                setter.setParameters(statement);
                statement.addBatch();
            }
            if (statement != null) {
                statement.executeBatch();
            }

        } catch (SQLException e) {
            logger.error("SQL exception: {}, error code: {}. \nException: {}", e.getMessage(), e.getErrorCode(), e);
            throw new DataStoreException(e);
        } finally {
            if (statement != null) {
                try {
                    statement.clearBatch();
                    if(!connection.getAutoCommit()) connection.commit();
                } catch (SQLException ignored) {
                }
            }

            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }


    /**
     * Update in batch! First enrich and then execute sql.
     *
     * @param sql query
     * @param updated Collection of updated objects
     * @param setter setter for prepared statement
     * @param enrich some {@link Enricher} for references
     * @throws DataStoreException
     */
    protected void updateBatch(String sql, Collection<E> updated, PreparedSetter setter, Enricher<E> enrich)
            throws DataStoreException {
        checkNotNull(updated, "Null collection");
        checkNotNull(setter, "Null prepared setter");
        checkNotNull(enrich, "Null enricher");


        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
            logger.debug("Execution updateBatch() of list with sql {}, and updated {} and PreparedSetter {}", sql, updated, setter);
            connection = getConnection();
            if(!connection.getAutoCommit()) {
                connection.setAutoCommit(false);
            }

            for(E el : updated)  {
                enrich.update(el); //todo
                statement = connection.prepareStatement(sql);
                setter.setParameters(statement);
                statement.addBatch();
            }
            if (statement != null) {
                statement.executeBatch();
            }

        } catch (SQLException e) {
            throw new DataStoreException(e);
        } finally {
            if (statement != null) {
                try {
                    statement.clearBatch();
                    if(!connection.getAutoCommit()) connection.commit();
                } catch (SQLException ignored) {
                }
            }

            GeneralUtil.closeInSilence(rs);
            GeneralUtil.closeInSilence(statement);
        }
    }


    /**
     *  Update one entity. Enricher will used before executing {@code sql} query.
     *
     * @param sql query
     * @param updatedObj object for updation references
     * @param enricher some {@link Enricher} for references
     * @param ps {@link PreparedSetter} setter for {@link PreparedStatement}
     * @return number of updated
     * @throws DataStoreException
     */
    protected int updateOne(String sql, E updatedObj, Enricher<E> enricher, PreparedSetter ps) throws DataStoreException {
        checkNotNull(updatedObj, "Null object");
        checkNotNull(enricher, "Null enricher");
        checkNotNull(ps, "null preparedSetter");
        Connection connection;
        PreparedStatement statement = null;
        int count;

        try {

            connection = getConnection();
            statement = connection.prepareStatement(sql);
            ps.setParameters(statement);
            enricher.update(updatedObj); /*First update references*/
            count = statement.executeUpdate();
            logger.debug("Execution updateOne() of list with sql {}, and updated obj {} and enricher {}. Updated: {}", sql, updatedObj, enricher, count);
            return count;
        } catch (SQLException e) {
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(statement);
        }
    }
        
        //Метод костыль, его надо выпилить попозже
        protected int updateOne1(String sql, E updatedObj, Enricher<E> enricher, PreparedSetter ps) throws DataStoreException {
        checkNotNull(updatedObj, "Null object");
        checkNotNull(enricher, "Null enricher");
        Connection connection;
        PreparedStatement statement = null;
        int count;

        try {

            connection = getConnection();
            statement = connection.prepareStatement(sql);
            ps.setParameters(statement);
            //enricher.update(updatedObj); /*First update references*/
            count = statement.executeUpdate();
            logger.debug("Execution updateOne() of list with sql {}, and updated obj {} and enricher {}. Updated: {}", sql, updatedObj, enricher, count);
            return count;
        } catch (SQLException e) {
            throw new DataStoreException(e);
        } finally {
            GeneralUtil.closeInSilence(statement);
        }}

    }


